package com.F05.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.log4j.Logger;

import com.F03.actionform.F0302ActionForm;
import com.F03.info.PdPlanningInfo;
import com.F05.actionform.F0503ActionForm;
import com.F05.info.FInvoicingProductionInfo;
import com.dao.DataOperations;
import com.dbtools.Dispose;
import com.exception.SelectException;
import com.util.CommonTools;

public class FInvoicingProductionDAO extends DataOperations{
	
	Logger log = Logger.getLogger(FInvoicingProductionDAO.class);
	public FInvoicingProductionDAO(){
		
	}
    public FInvoicingProductionDAO(Connection connection){
        super(connection);
    }
	
    public List getInvoiceFlagList() throws SelectException{
    	List list = new ArrayList();
    	StringBuffer sql = new StringBuffer();
    	sql.append(" select code,name from b_whether ");
    	ResultSet rest = null;
    	Map map=null;
    	try{
    		log.info(sql.toString());
    		rest = this.executeQuery(sql.toString());
    		while(rest.next()){
    			map = new HashMap();
    			map.put("CO", rest.getString("code"));
    			map.put("NA", rest.getString("name"));
    			list.add(map);
    		}
    	}catch(SQLException e){
    		e.printStackTrace();
    	}finally{
			Dispose.dispose(rest);
		}
    	return list;
    }
    
    public String initQuery(String manufacturingOrder) throws SelectException{
    	String reValue = "";
    	StringBuffer sql = new StringBuffer();
    	sql.append(" select cotract_number,customer_code,b.customers_name,unit_price,quantity,total_price,own_production from c_orders a,c_customers b ");
    	sql.append(" where a.customer_code = b.customers_code and  manufacturing_order ='"+manufacturingOrder+"'");		
    	
        ResultSet rest = null;
        try{
        	log.info(sql.toString());
        	
        	rest = this.executeQuery(sql.toString());
        	while(rest.next()){
        		reValue = CommonTools.null2String(rest.getString("customer_code"))+"*"+CommonTools.null2String(rest.getString("customers_name"))+"*"+CommonTools.null2String(rest.getString("cotract_number"))+"*"+CommonTools.null2String(rest.getString("unit_price"))+"*"+CommonTools.null2String(rest.getString("quantity"))+"*"+CommonTools.null2String(rest.getString("total_price"))+"*"+CommonTools.null2String(rest.getString("own_production"));
        	}
        }catch(SQLException e){
    		e.printStackTrace();
    	}finally{
			Dispose.dispose(rest);
		}
    	return reValue;
    }
    
	public List queryList(FInvoicingProductionInfo info) throws SelectException{
    	List list = new ArrayList();
    	StringBuffer sql = new StringBuffer();
    	sql.append(" select a.id,invoice_number,invoice_date,a.customers_code, manufacturing_order,");
    	sql.append(" unit_price,qty,total_rbm,total_usd, total_euros,");
        sql.append(" advanced_invoice,invoice_flag,contract_num,b.customers_name,c.name as advanced_invoice_name,d.name as invoice_flag_name ");
        sql.append(" from f_invoicing_production a,c_customers b,b_whether c,b_whether d ");
        sql.append(" where a.customers_code = b.customers_code and a.advanced_invoice = c.code and a.invoice_flag = d.code ");
 
    	if(!info.getContractNum1().equals("")){
    		sql.append(" and a.contract_num >="+info.getContractNum1());
    	}
    	if(!info.getContractNum2().equals("")){
    		sql.append(" and a.contract_num <="+info.getContractNum2());
    	}
    	if(!info.getInvoiceDate1().equals("")){
    		sql.append(" and a.invoice_date >= convert(datetime,'"+info.getInvoiceDate1()+"')");
    	}
    	if(!info.getInvoiceDate2().equals("")){
    		sql.append(" and a.invoice_date <= convert(datetime,'"+info.getInvoiceDate2()+"')");
    	}
    	if(!info.getManufacturingOrder1().equals("")){
    		sql.append(" and a.manufacturing_order >='"+info.getManufacturingOrder1()+"' ");
    	}
    	if(!info.getManufacturingOrder2().equals("")){
    		 sql.append(" and a.manufacturing_order <='"+info.getManufacturingOrder2()+"' ");
    	}
    	if(!info.getInvoiceNumber1().equals("")){
    		sql.append(" and a.invoice_number >='"+info.getInvoiceNumber1()+"' ");
    	}
    	if(!info.getInvoiceNumber2().equals("")){
    		sql.append(" and a.invoice_number <='"+info.getInvoiceNumber2()+"' ");
    	}
    	if(!info.getAdvancedInvoice().equals("")){
    		sql.append(" and a.advanced_invoice = '"+info.getAdvancedInvoice()+"'");
    	}
    	if(!info.getInvoiceFlag().equals("")){
    		sql.append(" and a.invoice_flag = '"+info.getInvoiceFlag()+"'");
    	}
    	
    	ResultSet rest = null;
    	try{
    		log.info(sql.toString());
    		rest = this.executeQuery(sql.toString());
    		while(rest.next()){
    			Map map = new HashMap();
    			map.put("id", CommonTools.null2String(rest.getString("id")));
    			map.put("invoiceNumber", CommonTools.null2String(rest.getString("invoice_number")));
				map.put("invoiceDate", CommonTools.getDateTime(rest.getDate("invoice_date")));
				map.put("customersCode", CommonTools.null2String(rest.getString("customers_code")));
				map.put("manufacturingOrder", CommonTools.null2String(rest.getString("manufacturing_order")));
				map.put("unitPrice", CommonTools.convertCurrencyRMB(rest.getString("unit_price")));
				map.put("qty", CommonTools.null2String(rest.getString("qty")));
				map.put("totalRbm", CommonTools.convertCurrencyRMB(rest.getString("total_rbm")));
				map.put("totalUsd", CommonTools.null2String(rest.getString("total_usd")));
				map.put("totalEuros", CommonTools.null2String(rest.getString("total_euros")));
				map.put("advancedInvoice", CommonTools.null2String(rest.getString("advanced_invoice")));
				map.put("invoiceFlag", CommonTools.null2String(rest.getString("invoice_flag")));
				map.put("contractNum", CommonTools.null2String(rest.getString("contract_num")));
				map.put("customersName", CommonTools.null2String(rest.getString("customers_name")));
				map.put("advancedInvoiceName", CommonTools.null2String(rest.getString("advanced_invoice_name")));
				map.put("invoiceFlagName", CommonTools.null2String(rest.getString("invoice_flag_name")));
				list.add(map);
    		}
    	}catch(SQLException e){
    		e.printStackTrace();
    	}finally{
			Dispose.dispose(rest);
		}
    	return list;
    }
	public List selectById(String id) throws SelectException{
    	List list = new ArrayList();
    	StringBuffer sql = new StringBuffer();    	
    	
    	sql.append(" select a.id,invoice_number,invoice_date,a.customers_code, a.manufacturing_order,");
    	sql.append(" a.unit_price,qty,total_rbm,total_usd, total_euros,");
        sql.append(" advanced_invoice,invoice_flag,contract_num,b.customers_name,c.total_price ");
        sql.append(" from f_invoicing_production a,c_customers b,c_orders c ");
        sql.append(" where a.customers_code = b.customers_code  ");
        sql.append(" and c.manufacturing_order = a.manufacturing_order");
        sql.append(" and a.id ="+id);
    	
    	ResultSet rest = null;
    	try {
    		log.info("sql is "+sql);
    		rest = this.executeQuery(sql.toString());
			while(rest.next()){
				Map map = new HashMap();
				map.put("id", CommonTools.null2String(rest.getString("id")));
				map.put("invoiceNumber", CommonTools.null2String(rest.getString("invoice_number")));
				map.put("invoiceDate", CommonTools.getDateTime(rest.getDate("invoice_date")));
				map.put("customersCode", CommonTools.null2String(rest.getString("customers_code")));
				map.put("manufacturingOrder", CommonTools.null2String(rest.getString("manufacturing_order")));
				//map.put("unitPrice", CommonTools.convertCurrencyRMB(rest.getString("unit_price")));
				map.put("unitPrice", CommonTools.null2String(rest.getString("unit_price")));
				map.put("qty", CommonTools.null2String(rest.getString("qty")));
				//map.put("totalRbm", CommonTools.convertCurrencyRMB(rest.getString("total_rbm")));
				map.put("totalRbm", CommonTools.null2String(rest.getString("total_rbm")));
				map.put("totalUsd", CommonTools.null2String(rest.getString("total_usd")));
				map.put("totalEuros", CommonTools.null2String(rest.getString("total_euros")));
				map.put("advancedInvoice", CommonTools.null2String(rest.getString("advanced_invoice")));
				map.put("invoiceFlag", CommonTools.null2String(rest.getString("invoice_flag")));
				map.put("contractNum", CommonTools.null2String(rest.getString("contract_num")));
				map.put("customersName", CommonTools.null2String(rest.getString("customers_name")));
				map.put("totalPrice", CommonTools.null2String(rest.getString("total_price")));
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			Dispose.dispose(rest);
		}
		return list;
    }
	
	public boolean delete(String id,F0503ActionForm form){
		boolean flag=true;
		StringBuffer sql=new StringBuffer();
		StringBuffer sql1 = new StringBuffer();
		List sqlList = new ArrayList();
		sql1.append(" update c_orders set state='3' ");
		sql1.append(" where manufacturing_order = (select manufacturing_order from f_invoicing_production where id ="+id+")");
		sqlList.add(sql1.toString());
		
		sql.append("DELETE FROM f_invoicing_production   ");
		sql.append("WHERE id ="+id);
		sqlList.add(sql.toString());
		
		try{
			log.info("sql is "+sql.toString());
			log.info("sql is ="+sql1.toString());
			flag=super.executeUpdate(sqlList,true);
			form.setErrorFlag("0");
		}catch(Exception e){
			e.printStackTrace();
			form.setErrorFlag("1");
		}
		return flag;
	}
	
	
	public boolean update(FInvoicingProductionInfo info,F0503ActionForm form){
    	boolean flag =false;
    	StringBuffer sql=new StringBuffer();
    	StringBuffer sql1 = new StringBuffer();
		List sqlList = new ArrayList();
		float totalRmb =0;
		
		sql.append("update f_invoicing_production set ");
		sql.append(" invoice_date =convert(datetime,'"+info.getInvoiceDate()+"'),");
		sql.append(" customers_code='"+info.getCustomerCode()+"',");
		sql.append(" manufacturing_order='"+info.getManufacturingOrder()+"',");
		sql.append(" qty ="+info.getQty());
		sql.append(" ,advanced_invoice='"+info.getAdvancedInvoice()+"'");
		sql.append(" ,contract_num ='"+info.getContractNum()+"'");
		sql.append(" ,unit_price='"+info.getUnitPrice()+"'");
		sql.append(" ,total_rbm='"+info.getTotalRbm()+"'");
		sql.append(" ,total_usd='"+info.getTotalUsd()+"'");
		sql.append(" ,total_euros='"+info.getTotalEuros()+"'");
		sql.append(" where id="+info.getId());
		log.info("sql is "+sql.toString());
		sqlList.add(sql.toString());
		try{
			totalRmb = judgeMoney(form);
			if((totalRmb-Float.parseFloat(form.getTotalRbmOther())+Float.parseFloat(form.getTotalRbm()))!=Float.parseFloat(form.getTotalPrice())){
				sql1.append(" update c_orders set state='3' ");
				sql1.append(" where manufacturing_order='"+info.getManufacturingOrder()+"'");
				sqlList.add(sql1.toString());
			}
			
			flag=super.executeUpdate(sqlList,true); 	
			form.setErrorFlag("0");
		}catch(Exception e){
	        e.printStackTrace();
	        form.setErrorFlag("1");
		}
		return flag;
    }
	public boolean insert(FInvoicingProductionInfo info,F0503ActionForm form){
		boolean flag=false;
		StringBuffer sql=new StringBuffer();
		StringBuffer sql1 = new StringBuffer();
		List sqlList = new ArrayList();
		float totalRmbOther = 0;
		try{

			sql.append("INSERT INTO f_invoicing_production  ");
			sql.append("  (invoice_number,invoice_date,customers_code, manufacturing_order,unit_price,qty,total_rbm,total_usd, total_euros,advanced_invoice,invoice_flag,contract_num ) ");
			sql.append("VALUES ('"+info.getInvoiceNumber()+"', convert(datetime,'"+info.getInvoiceDate()+"'), '"+info.getCustomerCode()+"'");
			sql.append(" ,'"+info.getManufacturingOrder()+"','"+info.getUnitPrice()+"',"+info.getQty()+",'"+info.getTotalRbm()+"','"+info.getTotalUsd()+"','"+info.getTotalEuros()+"','"+info.getAdvancedInvoice()+"','"+info.getInvoiceFlag()+"','"+info.getContractNum()+"')");
			
			sqlList.add(sql.toString());
			log.info("sql is "+sql.toString());
			totalRmbOther = judgeMoney(form);
			if((Float.parseFloat(info.getTotalRbm())+totalRmbOther)==Float.parseFloat(form.getTotalPrice())){
				sql1.append(" update c_orders set state='4' ");
				sql1.append(" where manufacturing_order='"+info.getManufacturingOrder()+"'");
				sqlList.add(sql1);
			}
			flag=super.executeUpdate(sqlList,true); 
			form.setErrorFlag("0");
		}catch(SQLException ex){
			if("1".equals(String.valueOf(ex.getErrorCode()))){
				//form.setErrorFlag("主键重复,错误序号: "+String.valueOf(ex.getErrorCode()));
				form.setErrorFlag("3");
			}else{
				form.setErrorFlag("1");
			}
		}catch(Exception e){
			e.printStackTrace();
			form.setErrorFlag("1");
		}
		return flag;
	}
	
	private float judgeMoney(F0503ActionForm form) throws SelectException{
		StringBuffer sql=new StringBuffer();
		ResultSet rest = null;
		float reValue =0;
		
		sql.append(" select sum(convert(float,total_rbm)) from f_invoicing_production ");
		sql.append(" where contract_num='"+form.getContractNum()+"'");
		sql.append(" and manufacturing_order='"+form.getManufacturingOrder()+"'");
		log.info("sql is ="+sql.toString());
		try{
			rest = this.executeQuery(sql.toString());
			while(rest.next()){
				reValue = rest.getFloat(1);
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			Dispose.dispose(rest);
		}
		return reValue;
	}
}
